package org.i_plus.model.dao;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.i_plus.model.GiftBean;
import org.i_plus.model.GiftDAO;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;

public class GiftDAOHibernate implements GiftDAO{
	private SessionFactory sessionFactory;
	

	public void setSessionFactory(SessionFactory sessionFactory) {
		this.sessionFactory = sessionFactory;
	}

	@Override
	public List<GiftBean> selectAll() {
		Session session = sessionFactory.getCurrentSession();
		Query query = session.createQuery("from GiftBean");
		return (List<GiftBean>)query.list();
	}

	@Override
	public GiftBean selectById(int giftid) {
		Session session =sessionFactory.getCurrentSession();
		GiftBean bean = (GiftBean)session.get(GiftBean.class, giftid);
		return bean;
	}

	@Override
	public void insertG(int giftid, String name, byte[] pic, int pop,
			int gmoney, int vip) {
		 Session session = sessionFactory.getCurrentSession();
		 GiftBean bean = new GiftBean();
		 bean.setGiftid(giftid);
		 bean.setName(name);
		 bean.setPic(pic);
		 bean.setPop(pop);
		 bean.setGmoney(gmoney);
		 bean.setVip(vip);
		 session.save(bean);
	}

	@Override
	public void updateG(int giftid, String name, byte[] pic, int pop,
			int gmoney, int vip) {
		 Session session = sessionFactory.getCurrentSession();
		 GiftBean bean = (GiftBean) session.get(GiftBean.class, giftid);
		 bean.setName(name);
		 bean.setPic(pic);
		 bean.setPop(pop);
		 bean.setGmoney(gmoney);
		 bean.setVip(vip);
		 session.update(bean);
		
	}

	@Override
	public void deleteG(int giftid) {
		Session session = sessionFactory.getCurrentSession();
		GiftBean bean = (GiftBean) session.get(GiftBean.class, giftid);
		session.delete(bean);
		
	}
	
	public int[] selectCount(){
		final String URL = "jdbc:sqlserver://localhost:1433;database=LovePlusPlus";
		final String USERNAME = "sa";
		final String PASSWORD = "passw0rd";
		int[] result = new int[5];
		Connection conn = null;
		PreparedStatement pstm = null;
		ResultSet rs = null;

		try {
			conn = DriverManager.getConnection(URL, USERNAME, PASSWORD);
			pstm = conn.prepareStatement("select sum(gcount) from Gift_Rcd group by giftid");		
			rs = pstm.executeQuery();
			int i = 0;
			while (rs.next()) {
				result[i] = rs.getInt(1);
				i++;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException se) {
					se.printStackTrace(System.err);
				}
			}
			if (pstm != null) {
				try {
					pstm.close();
				} catch (SQLException se) {
					se.printStackTrace(System.err);
				}
			}
			if (conn != null) {
				try {
					conn.close();
				} catch (Exception e) {
					e.printStackTrace(System.err);
				}
			}
		}
		return result;
	}

	public static void main(String[] args){
		ApplicationContext context = new ClassPathXmlApplicationContext("beans.config.xml");
    	SessionFactory session=(SessionFactory) context.getBean("sessionFactory");
		
		session.getCurrentSession().beginTransaction();
		GiftDAOHibernate gdao = (GiftDAOHibernate)context.getBean("GiftDAO");
//		gdao.insertG(4, "Choco", null, 3, 20, 2);
//		System.out.println(gdao.selectAll());
//		System.out.println(gdao.selectById(2));
//		gdao.updateG(2, "purse", null, 3, 100, 3);
//		gdao.deleteG(4);
		
		int[] beans = gdao.selectCount();
		for(int i=0;i<beans.length;i++)
			System.out.println(beans[i]);
		
		session.getCurrentSession().getTransaction().commit();
	}
}
